#coding=utf-8
import sqlite3
from decimal import Decimal
from stutils import csv2data, get_dbconnector,todec
from stsettings import datadir
from sohuprice import *
cns = {'1A0001':'szzs', '399001':'szcz'}
tb_index = '''
create table if not exists stock_index
(id integer primary key,
 name text not null,
 
 date text not null,
 
 start real not null,
 end real not null,
 high real not null,
 low real not null,
 
 volume integer not null,
 amount integer not null,
 fluctuation real not null,
 change real,
 
 unique (name, date)
);'''

def get_dbconnector(db=None):
    if not db:
        db = '%s/index.db'%datadir
    return sqlite3.connect(db)
    
def dzhprices2db(name, pl, con):
    cursor = con.cursor()
    pre = None; change = None
    
    for d in pl:
        
        date = d[0]
        
        start = float(d[1])
        high =  float(d[2])
        low = float(d[3])
        end = float(d[4])
        
        volume = int(d[6])*100
        amount = int( Decimal(d[5]) * 10000 )
        
        fluctuation = float( todec( (high-low)*100/end , 2) )
        if pre:
            change = float( todec( (end - pre) * 100/ pre, 2))
            
        cursor.execute('insert into stock_index\
                        (name, date, start,end, high, low, volume,amount,fluctuation,change)\
                        values(?, ?, ?, ?, ?, ?,?, ?, ?,?)', 
                       (name, date, start, end, high, low, volume,amount,fluctuation,change))
        pre = end
        
    con.commit()
            
def initdb(con):
    cursor = con.cursor()
    cursor.execute(tb_index)
    
    fn = '%s/dzh_szzs.csv'%datadir
    
    data = csv2data(fn)
    data.reverse()
    
    dzhprices2db('szzs', data, con)

def latest_end(con , name):
    c = con.cursor()
    c.execute('select end from stock_index where name=? order by date desc;', (name,))
    r = c.fetchone()
    if not r:return None
    return r[0]
         
def update(con,code, exdate=None):
    name = cns[code]
    if not exdate:
        import datetime
        exdate = datetime.date.today().isoformat()
    
    try:
        data = get_price_on(code, exdate)
    except NoPriceError,e:
        print "%s: 没有price数据 until now."%exdate
        return
    
    print data
    
    cursor = con.cursor()
    
    d = data
    date = d[0]
        
    start = float(d[1])
    high =  float(d[2])
    low = float(d[3])
    end = float(d[4])
    
    volume = int( float(d[5])*100 )
    amount = int( Decimal(d[6]) * 10000 )
    
    fluctuation = float( todec( (high-low)*100/end , 2) )
    
    pre_end = latest_end(con, name)
    change = None
    if pre_end:
        change = float( todec( (end - pre_end) * 100/ pre_end, 2))
    
    print change    
    cursor.execute('insert into stock_index\
                    (name, date, start,end, high, low, volume,amount,fluctuation,change)\
                    values(?, ?, ?, ?, ?, ?,?, ?, ?,?)', 
                    (name, date, start, end, high, low, volume,amount,fluctuation,change))
    con.commit()
    
def get_exdayslist(start='1990-12-19', end=None):
    con = get_dbconnector()
    
    c = con.cursor()
    if not end:
        c.execute('select date from stock_index where name=? and date>=? order by date', ('szzs',start))
    else:
        c.execute('select date from stock_index where name=? and date>=? and date<=? order by date', ('szzs',start,end))
    r = c.fetchall()
    
    if not r:return None
    r = [i[0] for i in r]
    
    c.close()
    con.close()
    
    return r

if __name__ == "__main__":
    con = get_dbconnector()
    update(con, '1A0001', '2008-01-16' )